const express = require('express');
const mysql = require('mysql2');
const bodyParser = require('body-parser');
const cors = require('cors');
const app = express();
const PORT = 3000;

// Middleware
app.use(bodyParser.json());
app.use(cors());


// Connect to the database
const connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    //password: '',
    database: 'project'
});

// Connect to the database
connection.connect((err) => {
    if (err) {
        console.error('Connection error: ' + err.stack);
        return;
    }
    console.log('Connected to MySQL database');
});


// Check the number of products in the database
connection.query('SELECT COUNT(*) AS count FROM products', (err, results) => {
    if (err) {
        console.error('Error checking products count:', err);
    } else {
        console.log('Number of products:', results[0].count); // print the count of products
    }
});


// Start the server
app.listen(PORT, () => {
    console.log(`Server started on http://localhost:${PORT}`);
});



// Route: Home
app.get('/', (req, res) => {
    res.send('Welcome to the Farm Store Management API!');
});

// Route: Get all products
app.get('/products', async (req, res) => {
    connection.execute('SELECT * FROM products', (err, results) => {
        if (err) {
            console.error('Error fetching products:', err);
            res.status(500).send('Internal Server Error');
        } else {
            res.json(results);
        }
    });
});

// Correct placement for the POST route to add a new product
app.post('/products', (req, res) => {
    const { name, unit_price, tax_rate, quantity } = req.body;
    const sql = 'INSERT INTO products (name, unit_price, tax_rate, quantity) VALUES (?, ?, ?, ?)';
    connection.execute(sql, [name, unit_price, tax_rate, quantity], (err, results) => {
        if (err) {
            console.error('Error adding product:', err);
            res.status(500).send('Internal Server Error');
        } else {
            res.status(201).send('Product added successfully');
        }
    });
});

// New endpoint for registering customers
app.post('/customers', (req, res) => { 
    const { gender, last_name, first_name } = req.body;

    if (!gender || !last_name || !first_name) {
        return res.status(400).send('Missing customer information');
    }

    const sql = 'INSERT INTO customers (gender, last_name, first_name) VALUES (?, ?, ?)';

    connection.execute(sql, [gender, last_name, first_name], (err, results) => {
        if (err) {
            console.error('Error adding customer:', err);
            return res.status(500).send('Internal Server Error');
        }
        res.status(201).send('Customer added successfully');
    });
});


// Route: Update stock
app.post('/stock', (req, res) => {
    const { product_id, quantity } = req.body;
    const sql = 'UPDATE stock SET quantity = quantity + ? WHERE product_id = ?';
    connection.execute(sql, [quantity, product_id], (err, results) => {
        if (err) {
            console.error('Error updating stock:', err);
            res.status(500).send('Internal Server Error');
        } else {
            res.send('Stock updated successfully');
        }
    });
});


